package com.landeqing.controller;

import com.landeqing.model.User;
import com.landeqing.service.impl.EcportExcleServiceImpl;
import com.landeqing.service.impl.ImportExcleServiceImpl;
import com.landeqing.utils.TooPoi;
import jxl.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("ecportAndecport")
public class ImportAndEcportExcleController {
    @Autowired
    private ImportExcleServiceImpl importExcleService;
    @Autowired
    private EcportExcleServiceImpl ecportExcleService;

    @RequestMapping("toSave")
    public ModelAndView toSave() {
        ModelAndView mav = new ModelAndView( "ecportExcle");

        return mav;
    }

    @RequestMapping("import")
    public ModelAndView save(@RequestParam(value = "fileName", required = false) MultipartFile file) {
        ModelAndView mav = new ModelAndView("importSuccess");
        try {
            if (file != null && file.getOriginalFilename() != null && file.getOriginalFilename().length() > 0) {
                List<User> userList = getAllByExcel(file.getInputStream());
                System.out.println("userList:" + userList);
                if (userList != null && userList.size() > 0) {
                    for (User user : userList) {
                        int id = user.getId();
                        User getByIdUser = importExcleService.idIsExist(id);
                        if (getByIdUser == null || getByIdUser.equals("")) {
                            int result = importExcleService.save(user);
                        } else {
                            getByIdUser.setUserName(user.getUserName());
                            getByIdUser.setPwd(user.getPwd());
                            int updateResult = importExcleService.update(getByIdUser);
                        }
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
       return mav;
    }

    @RequestMapping("ecport")
    public void ecport(HttpServletRequest request, HttpServletResponse response) {
        List<User> userList=ecportExcleService.getList();
        ModelAndView mav = new ModelAndView("ecportSuccess");
        SXSSFWorkbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("用户");
        try {
            //c创建工作表
           int cellIndex=0;
            Row rowHead=sheet.createRow(0);
            Cell cell0=rowHead.createCell(cellIndex++);
            cell0.setCellValue("编号（id）");
            Cell cell1=rowHead.createCell(cellIndex++);
            cell1.setCellValue("用户名（userName");
            Cell cell2=rowHead.createCell(cellIndex++);
            cell2.setCellValue("密码（pwd）");

            for (int i =0;i<userList.size();i++){
                User user=userList.get(i);
               Row row= sheet.createRow(i+1);
                Cell c0 = row.createCell(0);
                c0.setCellValue(user.getId()==0?"":user.getId()+"");
                Cell c1=row.createCell(1);
                c1.setCellValue(user.getUserName()==null?"":user.getUserName());
                Cell c2=row.createCell(2);
                c2.setCellValue(user.getPwd()==null?"":user.getPwd());
            }

            mav.addObject("userList",userList);
        }catch (Exception e ){
            e.printStackTrace();
        }
        String path= TooPoi.export(TooPoi.writeExcel(wb,"user"));

        try {
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="+ new String("user.xls".getBytes()));
//            response.addHeader("Content-Length", "" + fis.);
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }


//    public static List<User> getAllByExcel(String filePath) {
//        List<User> list = new ArrayList<User>();
//        try {
//            File file=new File(filePath);
//            if(!file.exists()){
//                file.createNewFile();
//            }
//            WritableWorkbook rwb= jxl.Workbook.createWorkbook(file);
//            WritableSheet rs = rwb.getSheet(0);//或者rwb.getSheet(0)
//            int clos = rs.getColumns();//得到所有的列
//            int rows = rs.getRows();//得到所有的行
//            System.out.println(clos + " rows:" + rows);
//            for (int i = 1; i < rows; i++) {
//                for (int j = 0; j < clos; j++) {
//                    //第一个是列数，第二个是行数
//                    String id = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
//                    String userName = rs.getCell(j++, i).getContents();
//                    String pwd = rs.getCell(j++, i).getContents();
//                    System.out.println("id:" + id + " userName:" + userName + " pwd:" + pwd);
//                    list.add(new User(Integer.parseInt(id), userName, pwd));
//                }
//            }
//        } catch (Exception e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//        return list;
//
//    }
//
    public static List<User> getAllByExcel(InputStream is) {
        List<User> list = new ArrayList<User>();
        try {
            Workbook rwb = Workbook.getWorkbook(is);
            jxl.Sheet rs = rwb.getSheet(0);//或者rwb.getSheet(0)
            int clos = rs.getColumns();//得到所有的列
            int rows = rs.getRows();//得到所有的行
            System.out.println(clos + " rows:" + rows);
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < clos; j++) {
                    //第一个是列数，第二个是行数
//                    String id = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                    String userName = rs.getCell(j++, i).getContents();
                    String pwd = rs.getCell(j++, i).getContents();
                    System.out.println(" userName:" + userName + " pwd:" + pwd);
                    list.add(new User(userName, pwd));
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }
}